//
//  AlbumDB.m
//  album
//
//  Created by 云天 李 on 12-1-12.
//  Copyright (c) 2012年 __MyCompanyName__. All rights reserved.
//

#import "AlbumDB.h"
#import "SQLiteHelper.h"
#import "Album.h"
#import "Picture.h"

@implementation AlbumDB

// 数据库连接
static sqlite3 *kAlbumDatabase;
// SQLite帮助类
static SQLiteHelper *kSqlite;

// 获取相册
static sqlite3_stmt *fetchAlbumsStatement = nil;
// 获取图片
static sqlite3_stmt *fetchPicturesStatement = nil;

// 释放资源
+ (void) finalizeStatements
{
	if (fetchAlbumsStatement)
	{
		sqlite3_finalize(fetchAlbumsStatement);
	}
	if (fetchPicturesStatement)
	{
		sqlite3_finalize(fetchPicturesStatement);
	}
    
    [kSqlite closeDatabase];
}

// 打开数据库（单例）
+ (id) singleton
{
	return [[[self alloc] init] autorelease];
}

-(id) init
{
	if ((self=[super init]) ) {
		if (kAlbumDatabase == nil)
		{
			if (kSqlite == nil) 
            {
				kSqlite = [[SQLiteHelper alloc] init];
			}
            
            [kSqlite createEditableCopyOfDatabaseIfNeeded];
			[kSqlite initDatabaseConnection];
			
			kAlbumDatabase = [kSqlite database];
		}
	}
	
	return self;
}

// 保存相册
+ (void) saveAlbum: (Album *) album
{
    if (album.albumid == 0)
    {
        [self addAlbum:album.title];
    }
    else
    {
        [self editAlbum:album];
    }
}

// 新建相册
+ (void) addAlbum: (NSString *) title
{
    sqlite3_stmt *statement;
    
    static char *sql = "INSERT INTO album (albumid,title) VALUES (NULL,?)";
    if (sqlite3_prepare_v2(kAlbumDatabase, sql, -1, &statement, NULL) != SQLITE_OK) 
    {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_bind_text(statement, 1, [title UTF8String], -1, NULL);
    
    int success = sqlite3_step(statement);
    
    if (success == SQLITE_ERROR) 
    {
        NSAssert1(0, @"Error: failed to add album with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_finalize(statement);
}

// 编辑相册
+ (void) editAlbum: (Album *) album
{
    sqlite3_stmt *statement;
    
    static char *sql = "UPDATE album SET title = ? WHERE albumid = ?";
    if (sqlite3_prepare_v2(kAlbumDatabase, sql, -1, &statement, NULL) != SQLITE_OK) 
    {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }    
    sqlite3_bind_text(statement, 1, [album.title UTF8String], -1, NULL);
    sqlite3_bind_int(statement, 2, album.albumid);
    
    int success = sqlite3_step(statement);
    
    if (success == SQLITE_ERROR) 
    {
        NSAssert1(0, @"Error: failed to edit album with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_finalize(statement);
}

// 删除相册
+ (void) deleteAlbum: (Album *) album
{
    sqlite3_stmt *statement;
    
    static char *sql = "DELETE FROM album WHERE albumid = ?";
    if (sqlite3_prepare_v2(kAlbumDatabase, sql, -1, &statement, NULL) != SQLITE_OK) 
    {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_bind_int(statement, 1, album.albumid);
    
    int success = sqlite3_step(statement);
    
    if (success == SQLITE_ERROR) 
    {
        NSAssert1(0, @"Error: failed to delete album with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_finalize(statement);
    
    // 删除此相册下的所有图片记录
    sqlite3_stmt *stmt;
    
    static char *sql2 = "UPDATE picture SET albumid = 0 WHERE albumid = ?";
    if (sqlite3_prepare_v2(kAlbumDatabase, sql2, -1, &stmt, NULL) != SQLITE_OK) 
    {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_bind_int(stmt, 1, album.albumid);
    
    success = sqlite3_step(stmt);
    
    if (success == SQLITE_ERROR) 
    {
        NSAssert1(0, @"Error: failed to delete pictures in album with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_finalize(stmt);
}

// 获取所有相册
+ (NSMutableArray *) fetchAlbums
{
    NSMutableArray *albums = [NSMutableArray array];
    
	// Compile the query for retrieving data.
	if (fetchAlbumsStatement == nil) {
		const char *sql = "SELECT albumid, title FROM album";
		if (sqlite3_prepare_v2(kAlbumDatabase, sql, -1, &fetchAlbumsStatement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
		}
	}
	
	while (sqlite3_step(fetchAlbumsStatement) == SQLITE_ROW)
	{
		Album *_album = [[Album alloc] init];
        
        _album.albumid = sqlite3_column_int(fetchAlbumsStatement, 0);
        _album.title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(fetchAlbumsStatement, 1)];
		
		[albums addObject:_album];
        [_album release];
	}
	
	// Reset the statement for future reuse.
	sqlite3_reset(fetchAlbumsStatement);
    
    return albums;
}

// 保存图片记录到某个相册
+ (void) savePictureToAlbum: (Picture *) picture
{
    sqlite3_stmt *statement;
    
    static char *sql = "INSERT INTO picture (albumid,picturename,size,width,height) VALUES (?,?,?,?,?)";
    if (sqlite3_prepare_v2(kAlbumDatabase, sql, -1, &statement, NULL) != SQLITE_OK) 
    {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_bind_int(statement, 1, picture.albumid);
    sqlite3_bind_text(statement, 2, [picture.picturename UTF8String], -1, NULL);
    sqlite3_bind_int(statement, 3, picture.size);
    sqlite3_bind_int(statement, 4, picture.width);
    sqlite3_bind_int(statement, 5, picture.height);
    
    
    int success = sqlite3_step(statement);
    
    if (success == SQLITE_ERROR) 
    {
        NSAssert1(0, @"Error: failed to save picture to album with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_finalize(statement);
}

// 获取某个相册下的图片
+ (NSMutableArray *)fetchPictures: (NSUInteger) albumid
{
    NSMutableArray *pictures = [NSMutableArray array];
    
	// Compile the query for retrieving data.
	if (fetchPicturesStatement == nil) {
		const char *sql = "SELECT pictureid,albumid,picturename,size,width,height FROM picture WHERE albumid=?";
		if (sqlite3_prepare_v2(kAlbumDatabase, sql, -1, &fetchPicturesStatement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
		}
	}
    sqlite3_bind_int(fetchPicturesStatement, 1, albumid);

	while (sqlite3_step(fetchPicturesStatement) == SQLITE_ROW)
	{
		Picture *_picture = [[Picture alloc] init];
        
        _picture.pictureid = sqlite3_column_int(fetchPicturesStatement, 0);
        _picture.albumid = sqlite3_column_int(fetchPicturesStatement, 1);
        _picture.picturename = [NSString stringWithUTF8String:(char *)sqlite3_column_text(fetchPicturesStatement, 2)];
		_picture.size = sqlite3_column_int(fetchPicturesStatement, 3);
        _picture.width = sqlite3_column_int(fetchPicturesStatement, 4);
        _picture.height = sqlite3_column_int(fetchPicturesStatement, 5);

		[pictures addObject:_picture];
        [_picture release];
	}
	
	// Reset the statement for future reuse.
	sqlite3_reset(fetchPicturesStatement);
    
    return pictures;
    
}

// 删除图片
+ (void)deletePicture: (Picture *) picture
{
    sqlite3_stmt *statement;
    
    static char *sql = "DELETE FROM picture WHERE pictureid = ?";
    if (sqlite3_prepare_v2(kAlbumDatabase, sql, -1, &statement, NULL) != SQLITE_OK) 
    {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_bind_int(statement, 1, picture.pictureid);
    
    int success = sqlite3_step(statement);
    
    if (success == SQLITE_ERROR) 
    {
        NSAssert1(0, @"Error: failed to delete picture with message '%s'.", sqlite3_errmsg(kAlbumDatabase));
    }
    
    sqlite3_finalize(statement);
}
@end
